# -*- coding: utf-8 -*-
# @Time    : 2021/3/17 9:30
# @Author  : YAQIWE
# @File    : excel_util.py
# @Software:  PyCharm
# @explain : excel操作

import re
import traceback
import openpyxl
from openpyxl.styles import Border, Side, colors
from openpyxl.styles import PatternFill

from util import logging

logging.basicConfig(level=logging.DEBUG)

class excel_util:

    def __init__(self, excel_path='../excel/AMZ账号在售情况-姚洁琪.xlsx'):
        self.__excel_path = excel_path
        self.wb = openpyxl.load_workbook(self.__excel_path)
        self.__tableInfo = {}

    def closeExcel(self):
        if None != self.wb:
            self.wb.close()
            self.wb = None

    def getMaxTable(self, sheetNum=0):
        info = self.__tableInfo.get(sheetNum)
        if info is None:
            sheet = self.wb.worksheets[sheetNum]
            info = (sheet.max_row, sheet.max_column)
            # 记录表格最大行和列
            self.__tableInfo = {sheetNum: info}
        return info

    def getRowsDate(self, rows=1, sheetNum=0):
        """
        获取某一行数据
        :param rows: 第i行数据
        :param sheetNum: 第i个工作表
        :return:
        """
        if rows < 1:
            logging.error(f'最小行数为1行，当前读取行数：{rows}')
            return False
        sheet = self.wb.worksheets[sheetNum]
        maxRows,maxColumn = self.getMaxTable(sheetNum)
        if rows > maxRows:
            logging.info('当前获取行数超过表格最大长度')
            return False
        datas = []
        for i in range(1, maxColumn + 1):
            data = sheet.cell(rows, i).value
            datas.append(data)
        logging.info(f'当前获取第{sheetNum}个工作表的第{rows}行数据:{repr(datas)}')
        return datas

    def getData(self, sheetNum=0):
        """
        读取excel表格的工作表数据返回
        :return: 二维数组
        """
        logging.info('获取表格数据')
        data = []
        try:
            sheet = self.wb.worksheets[sheetNum]
            for row in sheet.iter_rows():
                logging.info(row)
                rowData = []
                for cell in row:
                    rowData.append(cell.value)
                logging.info(f'数据表格行数:{repr(row)}，\n数据:{repr(rowData)}')
                data.append(rowData)
            logging.info('获取表格数据完成')
        except Exception as e:
            logging.error(f'读取数据发生错误:{e}')
        return data

    def saveData(self, data, sheetNum=0):
        logging.info('保存表格数据')
        rowL = len(data)
        # 1:红色 2:黄色 0:白色
        bgCol = {'1': PatternFill("solid", fgColor='FFFF00'),
                 '2': PatternFill("solid", fgColor='FF0000'),
                 '0': PatternFill("solid", fgColor='FFFFFF')}
        si = Side(style='thin', color=colors.BLACK)
        border_set = Border(left=si, right=si, top=si, bottom=si)
        try:
            try:
                wb = openpyxl.load_workbook(self.__excel_path)
                sheet = wb.worksheets[sheetNum]
                for i in range(1, rowL + 1):
                    logging.info(f'当前写入数据数据{data[i - 1]}')
                    cellL = len(data[i - 1])
                    for j in range(1, cellL + 1):
                        # 判断排名升降,排名降红色，排名升黄色，不变白色
                        try:
                            if (j == 8 or j == 9) and i > 1 and data[i - 1][j - 1] != None:
                                dataList = str(data[i - 1][j - 1]).split('||')
                                # 处理后的数据有两个，则该数据有颜色标记
                                if len(dataList) > 1:
                                    data[i - 1][j - 1] = dataList[0]
                                    sheet.cell(i, j).fill = bgCol[dataList[1]]
                        except Exception as e:
                            logging.error(f'处理数据{data[i - 1][j - 1]}发生错误：,行数：{i}，列数：{j},错误：{e}')
                        sheet.cell(i, j).value = data[i - 1][j - 1]
                        sheet.cell(i, j).border = border_set
                    # 一行一行保存
                    wb.save(self.__excel_path)
                logging.info('数据保存完成')
            finally:
                wb.close()
                logging.info('关闭文件')
        except Exception as e:
            logging.error(f'写入数据发生错误：{e},行数：{i}，列数：{j}')

    def saveCellData(self, data, row, column, *, bgColor=None, sheetNum=0):
        try:
            logging.info(f'保存单元格数据:{repr(data)},单元格颜色:{bgColor}，当前读写入行数：{row}，写入列数{column}')
            if row < 1 or column < 1:
                logging.error('写入表格位置错误')
                return False
            sheet = self.wb.worksheets[sheetNum]
            if None is not bgColor:
                # 检验十六进制颜色
                isColor = bool(re.match(r'[a-fA-F0-9]{6}$', bgColor))
                if isColor:
                    si = Side(style='thin', color=colors.BLACK)
                    border_set = Border(left=si, right=si, top=si, bottom=si)
                    # 设置单元格背景颜色
                    pat = PatternFill("solid", fgColor=bgColor)
                    sheet.cell(row, column).fill = pat
                    sheet.cell(row, column).border = border_set
            sheet.cell(row, column).value = data
            self.wb.save(self.__excel_path)
            logging.info('数据保存完成')
        except Exception as e:
            traceback.print_exc()
            logging.error(f'保存数据出错，行数：{row}，列数{column}，错误:{e}')

    def saveNewFile(self,newFileName):
        try:
            try:
                logging.info(f'重新保存文件：{self.__excel_path}，新文件名：{newFileName}')
                wbFile = openpyxl.load_workbook(self.__excel_path)
                wbFile.save(newFileName)
            finally:
                wbFile.close()
        except Exception:
            traceback.print_exc()

import time
if __name__ == '__main__':
    try:
        try:
            e = excel_util(excel_path='./excel/AMZ账号在售情况测试.xlsx')
            a,b = e.getMaxTable()
            data = e.getRowsDate(2)
            logging.info(f'测试读取excel数据:{data}')
            e.saveCellData('sdfdasf', 1, 1, bgColor='FAA233')
            e.saveCellData('111122233', 1, 2, bgColor='DAA233')
            thisTime = time.strftime("%Y%m%d", time.localtime())
            e.saveNewFile(f'./excel/AMZ账号在售情况-姚洁琪-{thisTime}.xlsx')
            # e.saveData(data=data)
        finally:
            e.closeExcel()
            logging.info('测试关闭excel表格')
    except Exception as e:
        traceback.print_exc()
        logging.error(f'测试excel操作时发生错误:{e}')
